import pandas as pd
import numpy as np
import warnings
warnings.filterwarnings("ignore")
import upsetplot
from upsetplot import generate_counts, plot, UpSet
from plotly_upset.plotting import plot_upset
from retentioneering.eventstream import Eventstream
df_clients_1 = pd.read_csv('df_clients_done_01.csv')
df_clients_2 = pd.read_csv('df_clients_done_02.csv')
clients = pd.concat([df_clients_1, df_clients_2])
columns = pd.DataFrame(clients.columns)
registration_dates = clients[['CustomerIdsMindboxId',
'CustomerCustomFieldsAnicuraRegistrationDate',
'CustomerCustomFieldsKinpetRegistrationDate',
'CustomerCustomFieldsLubimchikRegistrationDate',
'CustomerCustomFieldsPetstoryRegistrationDate',
'CustomerCustomFieldsRoyalcaninRegistrationDate']]
registration_dates.columns=['id', 'Anicura','Kinpet','Lubimchik','Petstory','Royalcanin']
registration_dates = pd.melt(registration_dates, id_vars=['id'], var_name='brand', value_name='timestamp')
registration_dates = registration_dates[registration_dates['timestamp'].notna()]
regs_1 = pd.read_csv('df_reg_done_01.csv')
regs_2 = pd.read_csv('df_reg_done_02.csv')
regs_3 = pd.read_csv('df_reg_done_03.csv')
regs = pd.concat([regs_1,regs_2,regs_3])
actions = regs[['CustomerActionCustomerIdsMindboxId','CustomerActionCreationDateTimeUtc','CustomerActionBrandIdsSystemName','CustomerActionActionTemplateName']]
actions.columns=['id','timestamp','brand','action']
first_action = actions.groupby('id').agg({'timestamp':'min'})
first_action = first_action.reset_index()
first_action = first_action.merge(actions, on=['id','timestamp'])
first_action['timestamp'] = pd.to_datetime(first_action['timestamp']).dt.date
df_orders = pd.read_csv('df_orders_other_brands.csv')
df_orders_royal = pd.read_csv('df_orders_royal_canin.csv')
df_orders_royal = df_orders_royal[['OrderCustomerIdsMindboxId','OrderFirstActionDateTimeUtc']]
df_orders_royal['brand'] = 'RoyalCanin'
df_orders_royal.columns=['id','timestamp','brand']
df_orders = df_orders[['OrderCustomerIdsMindboxId','OrderFirstActionDateTimeUtc','OrderFirstActionChannelIdsExternalId']]
df_orders.columns=['id','timestamp','brand']
df_orders = pd.concat([df_orders,df_orders_royal])
df_orders.loc[df_orders['brand'].str.contains('Lubimchik', na=False), 'brand'] = 'Lubimchik'
df_orders.loc[df_orders['brand'].str.contains('PetStory', na=False), 'brand'] = 'PetStory'
df_orders.loc[df_orders['brand'].str.contains('Kinpet', na=False), 'brand'] = 'Kinpet'
df_orders.loc[df_orders['brand'].str.contains('Anicura', na=False), 'brand'] = 'Anicura'
df_orders.loc[df_orders['brand'].str.contains('Белый клык', na=False), 'brand'] = 'Белый клык'
first_order = df_orders.groupby('id').agg({'timestamp':'min'})
first_order = first_order.reset_index()
first_order = first_order.merge(df_orders, on=['id','timestamp'])
registration_dates = pd.concat([first_action, first_order, registration_dates])
registration_dates['timestamp'] = pd.to_datetime(registration_dates['timestamp'])
registration_dates = registration_dates.replace({'Petstory':'PetStory'})
registration_dates = registration_dates[registration_dates['brand'].str.contains('Lubimchik|Anicura|PetNutrition|Kinpet|RoyalCanin|PetStory',na=False)]
registration_dates = registration_dates[['id','timestamp','brand']].drop_duplicates().sort_values(by=['id','timestamp']).drop_duplicates(subset=['id','brand'], keep='first')
registration_dates.columns=['user_id','timestamp','event']
stream = Eventstream(registration_dates)
stream\
.transition_graph(
edges_norm_type=None,
edges_weight_col='user_id'
)
<retentioneering.tooling.transition_graph.transition_graph.TransitionGraph at 0x2388354f520>
df_orders.columns=['user_id','timestamp','event']
stream = Eventstream(df_orders)
stream.transition_graph(
edges_norm_type=None,
edges_weight_col='user_id'
)
<retentioneering.tooling.transition_graph.transition_graph.TransitionGraph at 0x23884bb97c0>
contacts = clients[['CustomerIdsMindboxId','CustomerEmail','CustomerMobilePhone']]
contacts.columns=['id','email','phone']
anicura_clients = clients[['CustomerIdsMindboxId',
'CustomerIdsAnicuraID',
'CustomerCustomFieldsAnicuraEmail',
'CustomerCustomFieldsAnicuraMobilephone',
'CustomerCustomFieldsAnicuraRegistrationDate',
'CustomerCustomerSubscriptionsAnicuraIsSubscribed',
'CustomerCustomerSubscriptionsAnicuraSmsIsSubscribed',
'CustomerCustomerSubscriptionsAnicuraEmailIsSubscribed',
'CustomerCustomerSubscriptionsAnicuraViberIsSubscribed',
'CustomerCustomerSubscriptionsAnicuraMobilePushIsSubscribed',
'CustomerCustomerSubscriptionsAnicuraWebPushIsSubscribed']]
anicura_clients = pd.DataFrame(anicura_clients.groupby('CustomerIdsMindboxId').count().sum(axis=1))
anicura_clients = anicura_clients[anicura_clients[0]>0]
anicura_clients = clients[['CustomerIdsMindboxId',
'CustomerIdsAnicuraID',
'CustomerCustomFieldsAnicuraEmail',
'CustomerCustomFieldsAnicuraMobilephone',
'CustomerCustomFieldsAnicuraRegistrationDate',
'CustomerCustomerSubscriptionsAnicuraIsSubscribed',
'CustomerCustomerSubscriptionsAnicuraSmsIsSubscribed',
'CustomerCustomerSubscriptionsAnicuraEmailIsSubscribed',
'CustomerCustomerSubscriptionsAnicuraViberIsSubscribed',
'CustomerCustomerSubscriptionsAnicuraMobilePushIsSubscribed',
'CustomerCustomerSubscriptionsAnicuraWebPushIsSubscribed']]
anicura_clients = pd.DataFrame(anicura_clients.groupby('CustomerIdsMindboxId').count().sum(axis=1))
anicura_clients = anicura_clients[anicura_clients[0]>0]
anicura_clients[0] = 'Anicura'
anicura_clients = anicura_clients.reset_index()
anicura_clients.columns=['id','Anicura']
kinpet_clients = clients[['CustomerIdsMindboxId',
'CustomerCustomFieldsKinpetActivity',
'CustomerCustomFieldsKinpetBreedPet',
'CustomerCustomFieldsKinpetEmail',
'CustomerCustomFieldsKinpetIDrbc',
'CustomerCustomFieldsKinpetmobilephone',
'CustomerCustomFieldsKinpetMobilePhoneConfirmed',
'CustomerCustomFieldsKinpetNameCompany',
'CustomerCustomFieldsKinpetPermission',
'CustomerCustomFieldsKinpetPro',
'CustomerCustomFieldsKinpetRegisterConfirmed',
'CustomerCustomFieldsKinpetRegisteredFromLanding',
'CustomerCustomFieldsKinpetRegistrationDate',
'CustomerCustomFieldsKinpetSellerRole',
'CustomerCustomFieldsKinpetTelegram',
'CustomerCustomFieldsKinpetTypeClient',
'CustomerCustomFieldsKinpetTypeGroupCustomer',
'CustomerIdsKinpetContactID',
'CustomerIdsKinpetID',
'CustomerCustomerSubscriptionsKinpetIsSubscribed',
'CustomerCustomerSubscriptionsKinpetSmsIsSubscribed',
'CustomerCustomerSubscriptionsKinpetEmailIsSubscribed',
'CustomerCustomerSubscriptionsKinpetViberIsSubscribed',
'CustomerCustomerSubscriptionsKinpetMobilePushIsSubscribed',
'CustomerCustomerSubscriptionsKinpetWebPushIsSubscribed']]
kinpet_clients = pd.DataFrame(kinpet_clients.groupby('CustomerIdsMindboxId').count().sum(axis=1))
kinpet_clients = kinpet_clients[kinpet_clients[0]>0]
kinpet_clients[0] = 'Kinpet'
kinpet_clients = kinpet_clients.reset_index()
kinpet_clients.columns=['id','Kinpet']
lubimchik_clients = clients[['CustomerIdsMindboxId',
'CustomerCustomFieldsCITYLubimchik',
'CustomerCustomFieldsLubimchikAccountActivity',
'CustomerCustomFieldsLubimchikEmail',
'CustomerCustomFieldsLubimchikMobilephone',
'CustomerCustomFieldsLubimchikPermission',
'CustomerCustomFieldsLubimchikPL',
'CustomerCustomFieldsLubimchikRegistrationDate',
'CustomerCustomFieldsLubimchikTelegram',
'CustomerCustomFieldsRecentlyLubimchik',
'CustomerIdsLubimchikID',
'CustomerIdsLubimchikSailplay',
'CustomerCustomerSubscriptionsLubimchikIsSubscribed',
'CustomerCustomerSubscriptionsLubimchikSmsIsSubscribed',
'CustomerCustomerSubscriptionsLubimchikEmailIsSubscribed',
'CustomerCustomerSubscriptionsLubimchikViberIsSubscribed',
'CustomerCustomerSubscriptionsLubimchikMobilePushIsSubscribed',
'CustomerCustomerSubscriptionsLubimchikWebPushIsSubscribed']]
lubimchik_clients = pd.DataFrame(lubimchik_clients.groupby('CustomerIdsMindboxId').count().sum(axis=1))
lubimchik_clients = lubimchik_clients[lubimchik_clients[0]>0]
lubimchik_clients[0] = 'Lubimchik'
lubimchik_clients = lubimchik_clients.reset_index()
lubimchik_clients.columns=['id','Lubimchik']
petnutrition_clients = clients[['CustomerIdsMindboxId',
'CustomerCustomFieldsPetNutritionActivityStatus',
'CustomerCustomFieldsPetNutritionBrand',
'CustomerCustomFieldsPetNutritionCampaign1Status',
'CustomerCustomFieldsPetNutritionCampaign2Status',
'CustomerCustomFieldsPetNutritionCampaign3Status',
'CustomerCustomFieldsPetNutritionEmail',
'CustomerCustomFieldsPetNutritionEmailConfirmed',
'CustomerCustomFieldsPetNutritionFavoriteFood',
'CustomerCustomFieldsPetNutritionLastEcomSite',
'CustomerCustomFieldsPetNutritionLastEmailStatus',
'CustomerCustomFieldsPetNutritionMobilephone',
'CustomerCustomFieldsPetNutritionPermission',
'CustomerCustomFieldsPetNutritionPermissionPD',
'CustomerCustomFieldsPetNutritionShelter',
'CustomerIdsPetNutritionUserID',
'CustomerCustomerSubscriptionsPetNutritionIsSubscribed',
'CustomerCustomerSubscriptionsPetNutritionSmsIsSubscribed',
'CustomerCustomerSubscriptionsPetNutritionEmailIsSubscribed',
'CustomerCustomerSubscriptionsPetNutritionViberIsSubscribed',
'CustomerCustomerSubscriptionsPetNutritionMobilePushIsSubscribed',
'CustomerCustomerSubscriptionsPetNutritionWebPushIsSubscribed']]
petnutrition_clients = pd.DataFrame(petnutrition_clients.groupby('CustomerIdsMindboxId').count().sum(axis=1))
petnutrition_clients = petnutrition_clients[petnutrition_clients[0]>0]
petnutrition_clients[0] = 'PetNutrition'
petnutrition_clients = petnutrition_clients.reset_index()
petnutrition_clients.columns=['id','PetNutrition']
petstory_clients = clients[['CustomerIdsMindboxId',
'CustomerCustomFieldsPetStoryApproval',
'CustomerCustomFieldsPetStoryEmail',
'CustomerCustomFieldsPetStoryMobilephone',
'CustomerCustomFieldsPetStoryPermission',
'CustomerCustomFieldsPetStoryPushNotifications',
'CustomerIdsPetStoryOwnerID',
'CustomerCustomerSubscriptionsPetStoryIsSubscribed',
'CustomerCustomerSubscriptionsPetStorySmsIsSubscribed',
'CustomerCustomerSubscriptionsPetStoryEmailIsSubscribed',
'CustomerCustomerSubscriptionsPetStoryViberIsSubscribed',
'CustomerCustomerSubscriptionsPetStoryMobilePushIsSubscribed',
'CustomerCustomerSubscriptionsPetStoryWebPushIsSubscribed',
'CustomerCustomFieldsPetstoryRegistrationDate',
'CustomerCustomFieldsPetstoryRegistrationSource',]]
petstory_clients = pd.DataFrame(petstory_clients.groupby('CustomerIdsMindboxId').count().sum(axis=1))
petstory_clients = petstory_clients[petstory_clients[0]>0]
petstory_clients[0] = 'PetStory'
petstory_clients = petstory_clients.reset_index()
petstory_clients.columns=['id','PetStory']
royalcanin_clients = clients[['CustomerIdsMindboxId',
'CustomerCustomFieldsRoyalCaninBreederClub',
'CustomerCustomFieldsRoyalCaninBreedsCats',
'CustomerCustomFieldsRoyalCaninBreedsDogs',
'CustomerCustomFieldsRoyalCaninContactType',
'CustomerCustomFieldsRoyalCaninemail',
'CustomerCustomFieldsRoyalCaninEmailConfirmed',
'CustomerCustomFieldsRoyalCaninFiasArea',
'CustomerCustomFieldsRoyalCaninFiasCity',
'CustomerCustomFieldsRoyalCaninFiasRegion',
'CustomerCustomFieldsRoyalCaninIS18',
'CustomerCustomFieldsRoyalCaninMARSconsentdate',
'CustomerCustomFieldsRoyalCaninmobilephone',
'CustomerCustomFieldsRoyalCaninMobilePhoneConfirmed',
'CustomerCustomFieldsRoyalCaninPermission',
'CustomerCustomFieldsRoyalCaninPetOwner',
'CustomerCustomFieldsRoyalCaninSource',
'CustomerCustomFieldsRoyalCaninWorkPlace',
'CustomerIdsRoyalCaninCRMID',
'CustomerCustomerSubscriptionsRoyalCaninIsSubscribed',
'CustomerCustomerSubscriptionsRoyalCaninSmsIsSubscribed',
'CustomerCustomerSubscriptionsRoyalCaninEmailIsSubscribed',
'CustomerCustomerSubscriptionsRoyalCaninViberIsSubscribed',
'CustomerCustomerSubscriptionsRoyalCaninMobilePushIsSubscribed',
'CustomerCustomerSubscriptionsRoyalCaninWebPushIsSubscribed',
'CustomerCustomFieldsRoyalcaninRegistrationDate']]
royalcanin_clients = pd.DataFrame(royalcanin_clients.groupby('CustomerIdsMindboxId').count().sum(axis=1))
royalcanin_clients = royalcanin_clients[royalcanin_clients[0]>0]
royalcanin_clients[0] = 'RoyalCanin'
royalcanin_clients = royalcanin_clients.reset_index()
royalcanin_clients.columns=['id','RoyalCanin']
royalcanin_clients = clients[['CustomerIdsMindboxId',
'CustomerCustomFieldsRoyalCaninBreederClub',
'CustomerCustomFieldsRoyalCaninBreedsCats',
'CustomerCustomFieldsRoyalCaninBreedsDogs',
'CustomerCustomFieldsRoyalCaninContactType',
'CustomerCustomFieldsRoyalCaninemail',
'CustomerCustomFieldsRoyalCaninEmailConfirmed',
'CustomerCustomFieldsRoyalCaninFiasArea',
'CustomerCustomFieldsRoyalCaninFiasCity',
'CustomerCustomFieldsRoyalCaninFiasRegion',
'CustomerCustomFieldsRoyalCaninIS18',
'CustomerCustomFieldsRoyalCaninMARSconsentdate',
'CustomerCustomFieldsRoyalCaninmobilephone',
'CustomerCustomFieldsRoyalCaninMobilePhoneConfirmed',
'CustomerCustomFieldsRoyalCaninPermission',
'CustomerCustomFieldsRoyalCaninPetOwner',
'CustomerCustomFieldsRoyalCaninSource',
'CustomerCustomFieldsRoyalCaninWorkPlace',
'CustomerIdsRoyalCaninCRMID',
'CustomerCustomerSubscriptionsRoyalCaninIsSubscribed',
'CustomerCustomerSubscriptionsRoyalCaninSmsIsSubscribed',
'CustomerCustomerSubscriptionsRoyalCaninEmailIsSubscribed',
'CustomerCustomerSubscriptionsRoyalCaninViberIsSubscribed',
'CustomerCustomerSubscriptionsRoyalCaninMobilePushIsSubscribed',
'CustomerCustomerSubscriptionsRoyalCaninWebPushIsSubscribed',
'CustomerCustomFieldsRoyalcaninRegistrationDate']]
royalcanin_clients = pd.DataFrame(royalcanin_clients.groupby('CustomerIdsMindboxId').count().sum(axis=1))
royalcanin_clients = royalcanin_clients[royalcanin_clients[0]>0]
royalcanin_clients[0] = 'RoyalCanin'
royalcanin_clients = royalcanin_clients.reset_index()
royalcanin_clients.columns=['id','RoyalCanin']
brands_clients = anicura_clients.merge(
kinpet_clients, on='id', how='outer').merge(
lubimchik_clients, on='id', how='outer').merge(
petnutrition_clients, on='id', how='outer').merge(
petstory_clients, on='id', how='outer').merge(
royalcanin_clients, on='id', how='outer')
brands_amount_by_clients = pd.DataFrame(brands_clients.groupby('id').count().sum(axis=1)).reset_index()
brands_amount_by_clients.columns = ['id','brands_amount']
brands_clients = brands_clients.merge(brands_amount_by_clients, on=['id'])
brands_amount_contribution = brands_amount_by_clients.groupby('brands_amount').agg({'id':'nunique'})
brands_amount_contribution['%'] = brands_amount_contribution['id']/brands_amount_contribution['id'].sum()*100
brands_amount_contribution = brands_amount_contribution.reset_index()
active = df_orders[df_orders['timestamp'] >= '2023-05-01'].groupby('event').agg({'user_id':'nunique'})
active = active.reset_index()
active.columns=['brand','6-Month Active Customers']
import plotly.graph_objects as go
labels = ['1','2','>3']
values = [1078644, 26141, 991]
fig = go.Figure(data=[go.Pie(labels=labels, values=values,
marker=dict(colors=['#666','#e2001a','#FFC069']),
textfont_size=15,
pull=[0, 0, 0.2],
hoverinfo='label+value'
)
])
fig.update_layout(legend=dict(
orientation="h",
yanchor="bottom",
y=1.02,
xanchor="right",
x=1
))
fig.update_layout(title='Customer Distribution by Number of Brands<br>[without RoyalCanin]',
font=dict(size=12, color="black"))
fig.show("notebook")
brands_clients = brands_clients.merge(contacts, on='id', how='left')
clients_by_brands = pd.DataFrame(brands_clients.count()).reset_index()[1:7]
clients_by_brands.columns=['brand','customers']
clients_by_brands['доля от базы'] = clients_by_brands['customers'] / len(brands_clients) * 100
#clients_by_brands['доля от базы'] = round(clients_by_brands['доля от базы']).astype(str) + '%'
clients_by_brands_1 = pd.DataFrame(brands_clients[brands_clients['brands_amount']==1].count()).reset_index()[1:7]
clients_by_brands_1.columns=['brand','only 1']
clients_by_brands_with_contacts = pd.DataFrame(brands_clients[brands_clients['email'].notna()|brands_clients['phone'].notna()].count()).reset_index()[1:7]
clients_by_brands_with_contacts.columns=['brand','with contacts']
clients_by_brands = clients_by_brands.merge(clients_by_brands_1,on='brand').merge(clients_by_brands_with_contacts, on='brand').merge(active, on='brand', how='left')
clients_by_brands = clients_by_brands.sort_values(by='customers',ascending=False)
import plotly.graph_objects as go
import pandas as pd
df = clients_by_brands
fig = go.Figure(data=[go.Table(
header=dict(values=['Brand','Customers','% of Total','Single-Brand Customers', "With Contacts", 'Last 6-Month Customers'],
font_color='white',
fill_color='#666',
align='left'),
cells=dict(values=[df.brand, df.customers, round(df['доля от базы'],1).astype(str)+'%', df['only 1'], df['with contacts'], df['6-Month Active Customers']],
fill_color='white',
align='left'))
])
fig.update_layout(title='Brand Preferences Among Customers',
font=dict(size=12, color="black"))
fig.show("notebook")
brands_clients = brands_clients.fillna('')
venn_viz = brands_clients.copy()
venn_viz = venn_viz.replace({'':0,
'Anicura':1,
'Kinpet':1,
'Lubimchik':1,
'PetNutrition':1,
'PetStory':1,
'RoyalCanin':1
})
venn_viz = venn_viz[['RoyalCanin','PetNutrition','PetStory','Lubimchik','Kinpet','Anicura']]
fig = plot_upset(
dataframes=[venn_viz],
legendgroups=[''],
marker_size=10,
sorted_x="d",
horizontal_spacing = 0.075,
vertical_spacing = 0.1,
column_widths=[0.2, 0.8],
exclude_zeros=True,
marker_colors=['#666']
)
fig.update_layout(
width=1300,
height=700,
#font_family="Jetbrains Mono",
)
fig.layout.margin.update({'t':80, 'b':10,'l':50})
fig.update_layout(title='Brand Customer Intersection Scheme',
font=dict(size=12, color="black"))
fig.update_traces(hovertemplate="%{y}")
fig.show("notebook")
venn_viz = brands_clients[brands_clients['brands_amount']==2].copy()
venn_viz = venn_viz.replace({'':0,
'Anicura':1,
'Kinpet':1,
'Lubimchik':1,
'PetNutrition':1,
'PetStory':1,
'RoyalCanin':1
})
venn_viz = venn_viz[['RoyalCanin','PetNutrition','PetStory','Lubimchik','Kinpet','Anicura']]
fig = plot_upset(
dataframes=[venn_viz],
legendgroups=[''],
marker_size=10,
sorted_x="d",
horizontal_spacing = 0.075,
vertical_spacing = 0.1,
column_widths=[0.2, 0.8],
exclude_zeros=True,
marker_colors=['#666']
)
fig.update_layout(
width=1000,
height=700,
#font_family="Jetbrains Mono",
)
fig.layout.margin.update({'t':80, 'b':10,'l':50})
fig.update_layout(title='Brand Customer Intersection Scheme<br>[by 2]',
font=dict(size=12, color="black"))
fig.update_traces(hovertemplate="%{y}")
fig.show("notebook")
venn_viz = brands_clients[brands_clients['brands_amount']==3].copy()
venn_viz = venn_viz.replace({'':0,
'Anicura':1,
'Kinpet':1,
'Lubimchik':1,
'PetNutrition':1,
'PetStory':1,
'RoyalCanin':1
})
venn_viz = venn_viz[['RoyalCanin','PetNutrition','PetStory','Lubimchik','Kinpet','Anicura']]
fig = plot_upset(
dataframes=[venn_viz],
legendgroups=[''],
marker_size=10,
sorted_x="d",
horizontal_spacing = 0.075,
vertical_spacing = 0.1,
column_widths=[0.2, 0.8],
exclude_zeros=True,
marker_colors=['#666']
)
fig.update_layout(
width=1000,
height=700,
#font_family="Jetbrains Mono",
)
fig.layout.margin.update({'t':80, 'b':10,'l':50})
fig.update_layout(title='Brand Customer Intersection Scheme<br>[by 3]',
font=dict(size=12, color="black"))
fig.update_traces(hovertemplate="%{y}")
fig.show("notebook")
clients_id = clients[['CustomerIdsMindboxId',
'CustomerIdsAnicuraID',
'CustomerIdsKinpetID',
'CustomerIdsCraveID',
'CustomerIdsLubimchikID',
'CustomerIdsPetNutritionUserID',
'CustomerIdsPetStoryOwnerID',
'CustomerIdsRoyalCaninCRMID']]
clients_id = clients_id.rename(columns = {'CustomerIdsMindboxId':'ID',
'CustomerIdsAnicuraID':'AnicuraID',
'CustomerIdsKinpetID':'KinpetID',
'CustomerIdsCraveID':'CraveID',
'CustomerIdsLubimchikID':'LubimchikID',
'CustomerIdsPetNutritionUserID':'PetNutritionID',
'CustomerIdsPetStoryOwnerID':'PetStoryID',
'CustomerIdsRoyalCaninCRMID':'RoyalCaninID'})
active_users = df_orders[df_orders['timestamp'] >= '2023-05-01'][['user_id']].drop_duplicates()
active_users.columns=['id']
active_users['active'] = True
brands_clients = brands_clients.merge(active_users, on=['id'], how='left')
subscriptions = clients[[
'CustomerCustomerSubscriptionsMarsIsSubscribed',
'CustomerCustomerSubscriptionsMarsSmsIsSubscribed',
'CustomerCustomerSubscriptionsMarsEmailIsSubscribed',
'CustomerCustomerSubscriptionsMarsViberIsSubscribed',
'CustomerCustomerSubscriptionsMarsMobilePushIsSubscribed',
'CustomerCustomerSubscriptionsMarsWebPushIsSubscribed',
'CustomerCustomerSubscriptionsRoyalCaninIsSubscribed',
'CustomerCustomerSubscriptionsRoyalCaninSmsIsSubscribed',
'CustomerCustomerSubscriptionsRoyalCaninEmailIsSubscribed',
'CustomerCustomerSubscriptionsRoyalCaninViberIsSubscribed',
'CustomerCustomerSubscriptionsRoyalCaninMobilePushIsSubscribed',
'CustomerCustomerSubscriptionsRoyalCaninWebPushIsSubscribed',
'CustomerCustomerSubscriptionsPetNutritionIsSubscribed',
'CustomerCustomerSubscriptionsPetNutritionSmsIsSubscribed',
'CustomerCustomerSubscriptionsPetNutritionEmailIsSubscribed',
'CustomerCustomerSubscriptionsPetNutritionViberIsSubscribed',
'CustomerCustomerSubscriptionsPetNutritionMobilePushIsSubscribed',
'CustomerCustomerSubscriptionsPetNutritionWebPushIsSubscribed',
'CustomerCustomerSubscriptionsKinpetIsSubscribed',
'CustomerCustomerSubscriptionsKinpetSmsIsSubscribed',
'CustomerCustomerSubscriptionsKinpetEmailIsSubscribed',
'CustomerCustomerSubscriptionsKinpetViberIsSubscribed',
'CustomerCustomerSubscriptionsKinpetMobilePushIsSubscribed',
'CustomerCustomerSubscriptionsLubimchikIsSubscribed',
'CustomerCustomerSubscriptionsLubimchikSmsIsSubscribed',
'CustomerCustomerSubscriptionsLubimchikEmailIsSubscribed',
'CustomerCustomerSubscriptionsLubimchikViberIsSubscribed',
'CustomerCustomerSubscriptionsLubimchikMobilePushIsSubscribed',
'CustomerCustomerSubscriptionsLubimchikWebPushIsSubscribed',
'CustomerCustomerSubscriptionsPetStoryIsSubscribed',
'CustomerCustomerSubscriptionsPetStorySmsIsSubscribed',
'CustomerCustomerSubscriptionsPetStoryEmailIsSubscribed',
'CustomerCustomerSubscriptionsPetStoryViberIsSubscribed',
'CustomerCustomerSubscriptionsPetStoryMobilePushIsSubscribed',
'CustomerCustomerSubscriptionsPetStoryWebPushIsSubscribed',
'CustomerCustomerSubscriptionsAnicuraIsSubscribed',
'CustomerCustomerSubscriptionsAnicuraSmsIsSubscribed',
'CustomerCustomerSubscriptionsAnicuraEmailIsSubscribed',
'CustomerCustomerSubscriptionsAnicuraViberIsSubscribed',
'CustomerCustomerSubscriptionsAnicuraMobilePushIsSubscribed',
'CustomerCustomerSubscriptionsAnicuraWebPushIsSubscribed']]
subscriptions = subscriptions[[
'CustomerCustomerSubscriptionsPetStorySmsIsSubscribed',
'CustomerCustomerSubscriptionsPetStoryViberIsSubscribed',
'CustomerCustomerSubscriptionsPetStoryMobilePushIsSubscribed',
'CustomerCustomerSubscriptionsPetStoryEmailIsSubscribed',
'CustomerCustomerSubscriptionsKinpetViberIsSubscribed',
'CustomerCustomerSubscriptionsKinpetEmailIsSubscribed',
'CustomerCustomerSubscriptionsKinpetMobilePushIsSubscribed',
'CustomerCustomerSubscriptionsAnicuraIsSubscribed',
'CustomerCustomerSubscriptionsRoyalCaninEmailIsSubscribed',
'CustomerCustomerSubscriptionsLubimchikEmailIsSubscribed',
'CustomerCustomerSubscriptionsPetNutritionEmailIsSubscribed'
]]
subscriptions.columns=[
'PetStory Sms',
'PetStory Viber',
'PetStory MobilePush',
'PetStory Email',
'Kinpet Viber and Sms',
'Kinpet Email',
'Kinpet MobilePush',
'Anicura All Channels',
'RoyalCanin Email',
'Lubimchik Email',
'PetNutrition Email']
subscriptions = subscriptions.replace({True:1,False:0})
subscriptions = subscriptions.fillna(0).astype(int)
subscriptions = subscriptions.reset_index().drop('index', axis=1)
subscriptions.sum().to_excel('all_subscriptions.xlsx')
subscriptions['sum'] = subscriptions.sum(axis=1)
subscriptions = subscriptions[subscriptions['sum']>=2]
subscriptions = subscriptions.drop('sum', axis=1)
fig = plot_upset(
dataframes=[subscriptions],
legendgroups=[''],
marker_size=10,
sorted_x="d",
horizontal_spacing = 0.075,
vertical_spacing = 0.1,
column_widths=[0.2, 0.8],
exclude_zeros=True,
marker_colors=['#666']
)
fig.update_layout(
width=1900,
height=900,
#font_family="Jetbrains Mono",
)
fig.layout.margin.update({'t':80, 'b':10,'l':50})
fig.update_layout(title='Channels Intersection Scheme',
font=dict(size=12, color="black"))
fig.update_traces(hovertemplate="%{y}")
fig.show("notebook")
df_orders['action'] = df_orders['event'] + ' : ' + 'Заказ'
regs['CustomerActionActionTemplateName'] = regs['CustomerActionBrandIdsSystemName'] + ' : ' + regs['CustomerActionActionTemplateName']
regs = regs[~regs['CustomerActionActionTemplateName'].str.contains('агрузка историч|Загрузка клиентов для рассылки|Регистрация клиента в операции|Создание клиента|Mars', na=False)]
actions_1 = df_orders[['user_id','action']]
actions_1.columns=['id','action']
actions_2 = regs[['CustomerActionCustomerIdsMindboxId','CustomerActionActionTemplateName']]
actions_2.columns=['id','action']
acions = pd.concat([actions_1,actions_2])
two_brands = brands_clients[brands_clients['brands_amount']==2]
two_brands_1 = two_brands[(two_brands['RoyalCanin']!='')&(two_brands['PetNutrition']!='')]
two_brands_1 = two_brands_1[['id']].merge(acions, on='id').drop_duplicates()
two_brands_1['action'].value_counts()
RoyalCanin : Заказ 390 PetNutrition : Добавление питомца в список (импорт исторического) 3 Белый клык : Заказ 1 Name: action, dtype: int64
two_brands_2 = two_brands[(two_brands['RoyalCanin']!='')&(two_brands['PetStory']!='')]
two_brands_2 = two_brands_2[['id']].merge(acions, on='id').drop_duplicates()
two_brands_2['action'].value_counts()
PetStory : Заказ 5768 RoyalCanin : Заказ 282 PetStory : Доставка заказа 241 PetStory : Добавление продукта в список в операции 'PetStory.AddPetToPetList' 204 PetStory : Отмена заказа 148 PetStory : Оформление заказа 119 PetStory : Оплата заказа 112 RoyalCanin : Добавление питомца в список (импорт исторического) 34 PetStory : Подписка клиента в операции 'PetStory.ProductFinderEnd' 26 PetStory : PetStory Форма подписки клиента 21 PetStory : Авторизовался в МП 13 PetStory : PetStory Оплата страховки 12 PetStory : Изменение заказа в операции PetStory.UpdateOrder 7 PetStory : Добавление питомца в список (импорт исторического) 3 Anicura : Оформление заказа 3 Белый клык : Заказ 3 PetStory : PetStory Открыл страницу "Определите заболевание питомца по симптомам" 2 Лаборатория : Заказ 1 Name: action, dtype: int64
two_brands_2 = two_brands[(two_brands['RoyalCanin']!='')&(two_brands['Lubimchik']!='')]
two_brands_2 = two_brands_2[['id']].merge(acions, on='id').drop_duplicates()
two_brands_2['action'].value_counts()
Lubimchik : Заказ 1111 Lubimchik : Оформление заказа 128 Lubimchik : Отмена заказа 73 Lubimchik : Доставка заказа 62 RoyalCanin : Заказ 35 Lubimchik : Изменение заказа в операции Lubimchik.UpdateOrder 5 RoyalCanin : Добавление питомца в список (импорт исторического) 2 Белый клык : Заказ 1 Name: action, dtype: int64
two_brands_2 = two_brands[(two_brands['RoyalCanin']!='')&(two_brands['Kinpet']!='')]
two_brands_2 = two_brands_2[['id']].merge(acions, on='id').drop_duplicates()
two_brands_2['action'].value_counts()
Kinpet : Заказ 3707 Kinpet : Изменение заказа в операции Kinpet.UpdateOrder 123 RoyalCanin : Заказ 108 Kinpet : Kinpet Авторизация на сайте 58 Kinpet : Оформление заказа 34 Kinpet : Доставка заказа 24 RoyalCanin : Добавление питомца в список (импорт исторического) 24 Kinpet : Kinpet Форма подписки клиента 17 Kinpet : Отмена заказа 5 PetStory : Заказ 1 PetStory : Оформление заказа 1 Name: action, dtype: int64